package com.zywl.utils.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Microsoft Office excel 2003 Helper
 *
 * @author hh
 * @date Oct 13, 2010 2:28:01 PM
 */
public class MSExcelUtil {

    /**
     * 给定一个xsl 文件的路径，该函数会忽略空行，空列会添加一个空字符串代替。
     *
     * @param classPathFileName 相对于ClassPath的文件路径
     * @param sheetIndex        指定解析第几个sheet的工作区
     * @param columns           指定每行的列数，如果传入-1， 则根据内容来判断。
     * @return 将内容转换为集合
     */
    public static List<List<String>> getXlsText(String classPathFileName, int sheetIndex, int columns)
            throws IOException {
        InputStream is = MSExcelUtil.class.getResourceAsStream(classPathFileName);
        return readXlsText(is, sheetIndex, columns);
    }

    /**
     * 给定一个excel文件，获取excel文件的内容 支持xls 和 xlsx 格式
     *
     * @param fullFilePath 绝对路径
     * @param sheetIndex
     * @param columns
     * @return
     * @throws IOException
     */
    public static List<List<String>> getXlsTextByFullFilePath(String fullFilePath, int sheetIndex, int columns)
            throws IOException {
        File file = new File(fullFilePath);
        String fileName = file.getName();
        String type = fileName.substring(fileName.lastIndexOf(".") + 1);
        InputStream in = new FileInputStream(file);
        if ("xls".equals(type)) {
            return readXlsText(in, sheetIndex, columns);
        } else if ("xlsx".equals(type)) {
            return readXlsxText(in, sheetIndex, columns);
        } else {
            in.close();
            throw new IOException("文件格式不正确！！");
        }

    }

    public static List<List<String>> readXlsxText(InputStream is, int sheetIndex, int columns) throws IOException {
        List<List<String>> rows = new ArrayList<List<String>>();
        List<String> cells = null;
        Workbook wb = new XSSFWorkbook(is);

        // 获取xsl的工作区数量
        int sheets = wb.getNumberOfSheets(), cellLength = 0;
        if (sheets < sheetIndex) {
            wb.close();
            is.close();
            return null;
        }
        // 获取指定位置的工作区
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Iterator<Row> iteRow = sheet.rowIterator();
        while (iteRow.hasNext()) {
            row = iteRow.next();
            cells = new ArrayList<String>();
            cellLength = (columns == -1) ? row.getLastCellNum() : columns;
            for (int j = 0; j <= cellLength; j++) {
                Cell cell = row.getCell(j);
                if (cell != null && HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cells.add((cell == null ? ""
                                : sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString()));
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cells.add((cell == null ? "" : df.format(cell.getNumericCellValue())));
                    }
                } else {
                    cells.add((cell == null ? "" : cell.toString()));
                }
            }
            // 将解析的一行添加至集合
            rows.add(cells);
        }
        wb.close();
        is.close();
        return rows;

    }

    /**
     * 给定一个xsl InputStream，该函数会忽略空行，空列会添加一个空字符串代替。
     *
     * @param is
     * @param sheetIndex
     * @param columns
     * @return
     * @throws IOException
     */
    public static List<List<String>> readXlsText(InputStream is, int sheetIndex, int columns) throws IOException {
        List<List<String>> rows = new ArrayList<List<String>>();
        List<String> cells = null;
        HSSFWorkbook work = new HSSFWorkbook(is);

        // 获取xsl的工作区数量
        int sheets = work.getNumberOfSheets(), cellLength = 0;
        if (sheets < sheetIndex) {
            work.close();
            is.close();
            return null;
        }
        // 获取指定位置的工作区
        HSSFSheet sheet = work.getSheetAt(sheetIndex);
        HSSFRow row = null;
        Iterator<Row> iteRow = sheet.rowIterator();
        while (iteRow.hasNext()) {
            row = (HSSFRow) iteRow.next();
            cells = new ArrayList<String>();
            cellLength = (columns == -1) ? row.getLastCellNum() : columns;
            for (int j = 0; j <= cellLength; j++) {
                Cell cell = row.getCell(j);
                if (cell != null && HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cells.add((cell == null ? ""
                                : sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString()));
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cells.add(cell == null ? "" : df.format(cell.getNumericCellValue()));
                    }
                } else {
                    cells.add(cell == null ? "" : cell.toString());
                }
            }
            // 将解析的一行添加至集合
            rows.add(cells);
        }
        work.close();
        is.close();
        return rows;
    }

    /**
     * 获取xls的第1个sheet的内容
     *
     * @param classPathFileName xls相对于ClassPath的路径
     * @param columns           指定列数
     * @see #getXlsText(String, int, int)
     */
    public static List<List<String>> getXlsText(String classPathFileName, int columns) throws IOException {
        return getXlsText(classPathFileName, 0, columns);
    }

    /**
     * 获取xls的第1个sheet的内容
     *
     * @param classPathFileName xls相对于ClassPath的路径
     * @see #getXlsText(String, int, int)
     */
    public static List<List<String>> getXlsText(String classPathFileName) throws IOException {
        return getXlsText(classPathFileName, 0, -1);
    }

    /**
     * 将数据写入Excel表中
     *
     * @param workbook
     * @param data     数据
     */
    public static void writeTextForXls(HSSFWorkbook workbook, List<List<Object>> data) throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            List<Object> tmp = data.get(i);
            for (int j = 0; j < tmp.size(); j++) {
                setHSSFCellValue(tmp.get(j), row.createCell(j));
            }
        }
    }

    /**
     * 将数据写入Excel表中
     *
     * @param data 数据
     */
    public static HSSFWorkbook writeTextForXls(List<List<Object>> data) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            List<Object> tmp = data.get(i);
            for (int j = 0; j < tmp.size(); j++) {
                setHSSFCellValue(tmp.get(j), row.createCell(j));
            }
        }
        return workbook;
    }

    /**
     * 生成sheet表
     *
     * @param workbook
     * @param data
     * @return
     * @throws IOException
     */
    public static HSSFSheet writeSheetTextForxls(HSSFWorkbook workbook, List<List<Object>> data, String sheetName)
            throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        if (StringUtils.isNotEmpty(sheetName)) {
            workbook.setSheetName(0, sheetName);
        }
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            List<Object> tmp = data.get(i);
            for (int j = 0; j < tmp.size(); j++) {
                setHSSFCellValue(tmp.get(j), row.createCell(j));
            }
        }
        return sheet;
    }

    /**
     * 设置单元格的参数值
     *
     * @param value
     * @param cell
     */
    private static void setHSSFCellValue(Object value, HSSFCell cell) {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(value == null ? "" : value.toString()));
    }

    // 测试
    public static void main(String[] args) throws Exception {
        System.out.println(getXlsText("/user.xls"));
        System.out.println(getXlsText("/user.xls", 8)); // 指定每行的列数为8列
    }
}
